package com.source.labelAndBarcode.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.source.labelAndBarcode.entity.LabelingRecord;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.source.labelAndBarcode.vo.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.math.BigInteger;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author Joey
 * @since 2021-02-23
 */
@Repository
public interface LabelingRecordMapper extends BaseMapper<LabelingRecord> {

    @Select(value = "SELECT COUNT(*) FROM `labeling_record` WHERE stallsId = #{id} AND YEARWEEK(DATE_FORMAT(LabelingDate,'%Y-%m-%d'))=YEARWEEK(NOW()) " +
            "AND isDelete = 0")
    int weeklyRecord(Integer id);

    @Select(value = "SELECT COUNT(*) FROM `labeling_record` WHERE stallsId = #{id} AND DATE_FORMAT(LabelingDate,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m') " +
            "AND isDelete = 0 ")
    int monthlyRecord(Integer id);

    @Select(value = "SELECT a.poultryParentName, IFNULL(num, 0) num\n" +
            "FROM\n" +
            "( SELECT poultryCategoryParentId as id, poultryParentName FROM `poultry_category_parent`\n" +
            ") a LEFT JOIN\n" +
            "( SELECT pc.parentId poultryCategoryParentId, COUNT(lr.labelingRecordId) num\n" +
            "FROM `labeling_record` lr, `poultry_category` pc \n" +
            "WHERE stallsId = #{id} \n" +
            "AND TO_DAYS(LabelingDate)=TO_DAYS(NOW())\n" +
            "AND lr.`poultryCategoryId` = pc.`poultryCategoryId`\n" +
            "GROUP BY poultryCategoryParentId\n" +
            ") b\n" +
            "ON a.id = b.poultryCategoryParentId;")
    List<Map<String, BigInteger>> dailyRecordByPoultry(Integer id);

    @Select(value = "SELECT a.poultryParentName, IFNULL(num, 0) num\n" +
            "FROM\n" +
            "( SELECT poultryCategoryParentId as id, poultryParentName FROM `poultry_category_parent`\n" +
            ") a LEFT JOIN\n" +
            "( SELECT pc.parentId poultryCategoryParentId, COUNT(lr.labelingRecordId) num\n" +
            "FROM `labeling_record` lr, `poultry_category` pc \n" +
            "WHERE stallsId = #{id} \n" +
            "AND YEARWEEK(DATE_FORMAT(LabelingDate,'%Y-%m-%d'))=YEARWEEK(NOW())\n" +
            "AND lr.`poultryCategoryId` = pc.`poultryCategoryId`\n" +
            "GROUP BY poultryCategoryParentId\n" +
            ") b\n" +
            "ON a.id = b.poultryCategoryParentId;")
    List<Map<String, BigInteger>> weeklyRecordByPoultry(Integer id);

    @Select(value = "SELECT a.poultryParentName, IFNULL(num, 0) num\n" +
            "FROM\n" +
            "( SELECT poultryCategoryParentId as id, poultryParentName FROM `poultry_category_parent`\n" +
            ") a LEFT JOIN\n" +
            "( SELECT pc.parentId poultryCategoryParentId, COUNT(lr.labelingRecordId) num\n" +
            "FROM `labeling_record` lr, `poultry_category` pc \n" +
            "WHERE stallsId = #{id} \n" +
            "AND DATE_FORMAT(LabelingDate,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m')\n" +
            "AND lr.`poultryCategoryId` = pc.`poultryCategoryId`\n" +
            "GROUP BY poultryCategoryParentId\n" +
            ") b\n" +
            "ON a.id = b.poultryCategoryParentId;")
    List<Map<String, BigInteger>> monthlyRecordByPoultry(Integer id);

    @Select(value = "SELECT a.poultryParentName, b.toMarkName, IFNULL(num, 0) num\n" +
            "FROM\n" +
            "( SELECT poultryCategoryParentId, poultryParentName FROM `poultry_category_parent`\n" +
            ") a LEFT JOIN\n" +
            "( SELECT pc.parentId poultryCategoryParentId, tm.slaughterHouseName ToMarkName, COUNT(lr.labelingRecordId) num\n" +
            "FROM `labeling_record` lr, `poultry_category` pc, to_market tm\n" +
            "WHERE stallsId = #{id} \n" +
            "AND TO_DAYS(LabelingDate)=TO_DAYS(NOW())\n" +
            "AND lr.`poultryCategoryId` = pc.`poultryCategoryId`\n" +
            "AND lr.`toMarketId` = tm.`slaughterHouseId`\n" +
            "GROUP BY tm.slaughterHouseName, poultryCategoryParentId\n" +
            ") b\n" +
            "ON a.poultryCategoryParentId = b.poultryCategoryParentId\n" +
            "ORDER BY num DESC;")
    List<Map<Object, Object>> dailyRecordByPoultryAndToMark(Integer id);

    @Select(value = "SELECT a.poultryParentName, b.toMarkName, IFNULL(num, 0) num\n" +
            "FROM\n" +
            "( SELECT poultryCategoryParentId, poultryParentName FROM `poultry_category_parent`\n" +
            ") a LEFT JOIN\n" +
            "( SELECT pc.parentId poultryCategoryParentId, tm.slaughterHouseName ToMarkName, COUNT(lr.labelingRecordId) num\n" +
            "FROM `labeling_record` lr, `poultry_category` pc, to_market tm\n" +
            "WHERE stallsId = #{id} \n" +
            "AND YEARWEEK(DATE_FORMAT(LabelingDate,'%Y-%m-%d'))=YEARWEEK(NOW())\n" +
            "AND lr.`poultryCategoryId` = pc.`poultryCategoryId`\n" +
            "AND lr.`toMarketId` = tm.`slaughterHouseId`\n" +
            "GROUP BY tm.slaughterHouseName, poultryCategoryParentId\n" +
            ") b\n" +
            "ON a.poultryCategoryParentId = b.poultryCategoryParentId\n" +
            "ORDER BY num DESC ")
    List<Map<Object, Object>> weeklyRecordByPoultryAndToMark(Integer id);

    @Select(value = "SELECT a.poultryParentName, b.toMarkName, IFNULL(num, 0) num\n" +
            "FROM\n" +
            "( SELECT poultryCategoryParentId, poultryParentName FROM `poultry_category_parent`\n" +
            ") a LEFT JOIN\n" +
            "( SELECT pc.parentId poultryCategoryParentId, tm.slaughterHouseName ToMarkName, COUNT(lr.labelingRecordId) num\n" +
            "FROM `labeling_record` lr, `poultry_category` pc, to_market tm\n" +
            "WHERE stallsId = #{id} \n" +
            "AND DATE_FORMAT(LabelingDate,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m')\n" +
            "AND lr.`poultryCategoryId` = pc.`poultryCategoryId`\n" +
            "AND lr.`toMarketId` = tm.`slaughterHouseId`\n" +
            "GROUP BY tm.slaughterHouseName, poultryCategoryParentId\n" +
            ") b\n" +
            "ON a.poultryCategoryParentId = b.poultryCategoryParentId\n" +
            "ORDER BY num DESC;")
    List<Map<Object, Object>> monthlyRecordByPoultryAndToMark(Integer id);

    @Select(value = "SELECT stallsName, COUNT(*) barcodeCount FROM stalls, labeling_record lr\n" +
            "WHERE stalls.stallsId = lr.stallsId  AND stallsName LIKE #{keyWord} AND lr.labelingDate between #{startDate} and #{endDate} \n" +
            "AND stalls.isDelete = 0 AND lr.isDelete = 0 GROUP BY stalls.stallsId")
    IPage<BarcodeCountVo> findByLikeNameAndTimeOnPagination(Page<BarcodeCountVo> page, String keyWord, Date startDate, Date endDate);

    @Select(value = "SELECT stallsName, COUNT(*) barcodeCount FROM stalls, labeling_record lr\n" +
            "WHERE stalls.stallsId = lr.stallsId AND DATE_FORMAT( lr.labelingDate, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) " +
            "AND stalls.isDelete = 0 AND lr.isDelete = 0\n" +
            "GROUP BY stalls.stallsId")
    IPage<BarcodeCountVo> findMonthly(Page<BarcodeCountVo> page);

    @Select(value = "SELECT stallsName, COUNT(*) barcodeCount FROM stalls, labeling_record lr\n" +
            "WHERE stalls.stallsId = lr.stallsId " +
            "AND stalls.isDelete = 0 AND lr.isDelete = 0\n" +
            "GROUP BY stalls.stallsId")
    IPage<BarcodeCountVo> findAllOnPagination(Page<BarcodeCountVo> page);

    @Select(value = "SELECT stallsName, COUNT(*) barcodeCount FROM stalls, labeling_record lr\n" +
            "WHERE stalls.stallsId = lr.stallsId AND lr.labelingRecordId = #{id} " +
            "AND stalls.isDelete = 0 AND lr.isDelete = 0\n" +
            "GROUP BY stalls.stallsId")
    BarcodeCountVo findById(String id);

    String DRSelect = "SELECT lr.*, stallsName, poultryCategoryName, tm.slaughterHouseName toMarketName\n" +
            "FROM labeling_record lr, stalls, poultry_category pc, to_market tm \n" +
            "WHERE lr.stallsId = stalls.stallsId \n" +
            "AND pc.poultryCategoryId = lr.poultryCategoryId \n" +
            "AND tm.slaughterHouseId = lr.toMarketId AND lr.isDelete = 0 AND stalls.isDelete = 0 AND pc.isDelete = 0 AND tm.isDelete = 0 " +
            "AND stalls.status = 1 AND tm.status = 1  ";

    @Select(DRSelect)
    IPage<DetailedRecordVo> findAllDROnPagination(Page<DetailedRecordVo> page);

    @Select(value = DRSelect +
            "AND CONCAT(barcodeNumber,labelingDate,barcodeMachineNumber,stallsName, tm.slaughterHouseName, poultryCategoryName) LIKE #{keyWord}")
    IPage<DetailedRecordVo> findAllDRByLikeNameOnPagination(Page<DetailedRecordVo> page, String keyWord);

    @Select(value = DRSelect +
            "AND lr.labelingRecordId = #{id}")
    DetailedRecordVo findDetailedRecordById(String id);

    @Select("SELECT DISTINCT DATE_FORMAT(lr.labelingDate,'%Y-%m-%d') saleDate \n" +
            "from poultry_category_parent pcp, poultry_category pc, labeling_record lr, sys_user su, stalls\n" +
            "WHERE pc.parentId = pcp.poultryCategoryParentId \n" +
            "AND lr.poultryCategoryId = pc.poultryCategoryId\n" +
            "AND lr.labelingDate BETWEEN #{startDate} AND #{endDate} \n" +
            "AND lr.stallsId = stalls.stallsId AND stalls.userId = su.userId\n" +
            "AND stallsName LIKE #{stallsName} \n" +
            "AND su.`name` LIKE #{contacts} AND lr.isDelete = 0 AND pcp.isDelete = 0 AND pc.isDelete = 0 AND stalls.isDelete = 0 AND stalls.status = 1 \n" +
            "GROUP BY poultryParentName, saleDate ORDER BY saleDate DESC  ")
    IPage<PoultryDateSaleVo> findByMultiKeyWord(@Param("stallsName") String stallsName,
                                                @Param("contacts") String contacts,
                                                @Param("startDate") Date startDate,
                                                @Param("endDate") Date endDate,
                                                Page<PoultryDateSaleVo> page);

    @Select("SELECT DISTINCT DATE_FORMAT(lr.labelingDate,'%Y-%m-%d') saleDate \n" +
            "from poultry_category_parent pcp, poultry_category pc, labeling_record lr, sys_user su, stalls\n" +
            "WHERE pc.parentId = pcp.poultryCategoryParentId \n" +
            "AND lr.poultryCategoryId = pc.poultryCategoryId\n" +
            "AND lr.labelingDate BETWEEN #{startDate} AND #{endDate} \n" +
            "AND lr.stallsId = stalls.stallsId AND stalls.userId = su.userId\n" +
            "AND stallsName LIKE #{stallsName} \n" +
            "AND su.`name` LIKE #{contacts} AND lr.isDelete = 0 AND pcp.isDelete = 0 AND pc.isDelete = 0 AND stalls.isDelete = 0 AND stalls.status = 1 \n" +
            "GROUP BY poultryParentName, saleDate ORDER BY saleDate DESC  ")
    List<PoultryDateSaleVo> findByMultiKeyWordVer2(@Param("stallsName") String stallsName,
                                                @Param("contacts") String contacts,
                                                @Param("startDate") Date startDate,
                                                @Param("endDate") Date endDate);

    @Select("SELECT DATE_FORMAT(lr.labelingDate,'%Y-%m-%d') saleDate, count(*) saleCount, poultryParentName \n" +
            "from poultry_category_parent pcp, poultry_category pc, labeling_record lr, sys_user su, stalls\n" +
            "WHERE pc.parentId = pcp.poultryCategoryParentId \n" +
            "AND lr.poultryCategoryId = pc.poultryCategoryId\n" +
            "AND lr.labelingDate BETWEEN #{startDate} AND #{endDate} \n" +
            "AND lr.stallsId = stalls.stallsId AND stalls.userId = su.userId\n" +
            "AND stallsName LIKE #{stallsName} \n" +
            "AND su.`name` LIKE #{contacts} AND lr.isDelete = 0 AND pcp.isDelete = 0 AND pc.isDelete = 0 AND stalls.isDelete = 0 AND stalls.status = 1 \n" +
            "GROUP BY poultryParentName, saleDate ORDER BY saleDate DESC ")
    List<PoultryDateVo> findCountsByDay(String stallsName, String contacts, Date startDate, Date endDate);

    @Select("SELECT DATE_FORMAT(lr.labelingDate,'%Y-%m-%d') saleDate, count(*) saleCount, poultryCategoryParentId poultryParentId \n" +
            "from poultry_category_parent pcp, poultry_category pc, labeling_record lr, sys_user su, stalls\n" +
            "WHERE pc.parentId = pcp.poultryCategoryParentId \n" +
            "AND lr.poultryCategoryId = pc.poultryCategoryId\n" +
            "AND lr.labelingDate BETWEEN #{startDate} AND #{endDate} \n" +
            "AND lr.stallsId = stalls.stallsId AND stalls.userId = su.userId\n" +
            "AND stallsName LIKE #{stallsName} \n" +
            "AND su.`name` LIKE #{contacts} AND lr.isDelete = 0 AND pcp.isDelete = 0 AND pc.isDelete = 0 AND stalls.isDelete = 0 AND stalls.status = 1 \n" +
            "GROUP BY poultryParentId, saleDate ORDER BY saleDate DESC ")
    List<PoultryDateVo2> findCountsByDayVer2(String stallsName, String contacts, Date startDate, Date endDate);

    @Select("SELECT count(*) saleCount, poultryParentName \n" +
            "from poultry_category_parent pcp, poultry_category pc, labeling_record lr, sys_user su, stalls\n" +
            "WHERE pc.parentId = pcp.poultryCategoryParentId \n" +
            "AND lr.poultryCategoryId = pc.poultryCategoryId\n" +
            "AND lr.labelingDate BETWEEN #{startDate} AND #{endDate} \n" +
            "AND lr.stallsId = stalls.stallsId AND stalls.userId = su.userId\n" +
            "AND stallsName LIKE #{stallsName} \n" +
            "AND su.`name` LIKE #{contacts} AND lr.isDelete = 0 AND pcp.isDelete = 0 AND pc.isDelete = 0 AND stalls.isDelete = 0 AND stalls.status = 1 \n" +
            "GROUP BY poultryParentName ")
    IPage<PoultrySaleVo> findCountsByTime(@Param("stallsName") String stallsName,
                                          @Param("contacts") String contacts,
                                          @Param("startDate") Date startDate,
                                          @Param("endDate") Date endDate, Page<PoultrySaleVo> page);


    @Select(value = "SELECT count(*) saleCount, poultryCategoryParentId parentId \n" +
            "from poultry_category_parent pcp, poultry_category pc, labeling_record lr, sys_user su, stalls\n" +
            "WHERE pc.parentId = pcp.poultryCategoryParentId \n" +
            "AND lr.poultryCategoryId = pc.poultryCategoryId\n" +
            "AND lr.labelingDate BETWEEN #{startDate} AND #{endDate} \n" +
            "AND lr.stallsId = stalls.stallsId AND stalls.userId = su.userId\n" +
            "AND stallsName LIKE #{stallsName} \n" +
            "AND su.`name` LIKE #{contacts} AND lr.isDelete = 0 AND pcp.isDelete = 0 AND pc.isDelete = 0 AND stalls.isDelete = 0 AND stalls.status = 1 " +
            "GROUP BY parentId \n"
    )
    IPage<PoultrySaleVo2> findCounts(String stallsName, String contacts, Date startDate, Date endDate, Page<PoultrySaleVo2> page);
}
